package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.ReceiptService;
import cn.gson.crm.model.entity.Staff;
import cn.gson.crm.model.entity.TotalService;

public class TotalDao {
	private int pageSize=8;
	
	/**
	 * 收款记录
	 * @throws SQLException 
	 */
	public boolean receiptHis(Double receiptMoney,String totalId,String staffId) throws SQLException {
		String sql="insert into receipt (receipt_money,receipt_total_id,receipt_staff_id) values (?,?,?)";
		return Jdbc.getInstace().executeUpdate(sql,receiptMoney,totalId,staffId);
		
	}
	/**
	 * 缴费成功修改未交金额
	 * @throws SQLException 
	 */
	public boolean totalNoMoney(Double newNoMoney,String totalId) throws SQLException {
		String sql="update total set total_no_money= ? where total_id= ? ";
		return Jdbc.getInstace().executeUpdate(sql,newNoMoney,totalId);
	}
	/**
	 * 获取收款人
	 * @throws SQLException 
	 */
	public List<Staff> getPayees() throws SQLException {
		String sql="select * from staff";
		List<Map<String,Object>>list=Jdbc.getInstace().executeQueryForList(sql);
		return changeStaff(list);
		//return list;
		
	}
	
	/**
	 * 搜索总页数
	 */
	public int getTotalServicePage( String studentName) throws SQLException {
		String sql = "select count(*) totals from total "
				+ "INNER JOIN student ON total.total_student_id = student.student_id "
				+ "LEFT JOIN `group` b ON total.total_student_id = b.group_student_id "
				+ "LEFT JOIN clazz c ON b.group_clazz_id = c.clazz_id "
				+ "WHERE total.total_no_money<>0";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, studentName);
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer TotalService = Integer.parseInt(map.get("totals").toString());
		return (int) Math.ceil(TotalService.doubleValue() / pageSize);
	}
	/**
	 * 获取已交学员总页数
	 */
	public int getAllTotalServicePage( String staffName) throws SQLException {
		String sql = "select count(*) totals from total "
				+ "INNER JOIN student ON total.total_student_id = student.student_id "
				+ "LEFT JOIN `group` b ON total.total_student_id = b.group_student_id "
				+ "LEFT JOIN clazz c ON b.group_clazz_id = c.clazz_id "
				+ "where total.total_no_money = 0";
		List<Object> params = new ArrayList<>();
		sql = buildAllSqlAndParams(params, sql, staffName);
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer TotalService = Integer.parseInt(map.get("totals").toString());
		return (int) Math.ceil(TotalService.doubleValue() / pageSize);
	}
	/**
	 * 带条件分页查询
	 * @param type
	 * @param page
	 * @param userName
	 * @param dept
	 * @param tel
	 * @return
	 * @throws SQLException
	 */
	public List<TotalService> getPageTotalServiceListByType(int page, String studentName)
			throws SQLException {
		int start = (page - 1) * pageSize;
		//String sql = "select total.total_id,total.total_if_loan,total.total_money,total.total_no_money,total.total_staff_id,total.total_student_id,total.total_term,student.student_name,staff.staff_name from total INNER JOIN student ON total.total_student_id=student.student_id INNER JOIN staff  ON total.total_staff_id=staff.staff_id";
		String sql = "SELECT * FROM total "
				+ "INNER JOIN student ON total.total_student_id = student.student_id "
				+ "LEFT JOIN `group` b ON total.total_student_id = b.group_student_id "
				+ "LEFT JOIN clazz c ON b.group_clazz_id = c.clazz_id "
				+ "left JOIN staff on staff.staff_id=total.total_staff_id "
				+ "WHERE total.total_no_money <> 0";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, studentName);
		sql += " order by total_id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		return queryUserList(sql, params.toArray());
	}
	
	/**
	 * 已交完分页查询
	 * @param params
	 * @param sql
	 * @param studentName
	 * @return
	 */
	public List<TotalService> getAllPageTotalServiceListByType(int page, String studentName)
			throws SQLException {
		int start = (page - 1) * pageSize;
		//String sql = "select total.total_id,total.total_if_loan,total.total_money,total.total_no_money,total.total_staff_id,total.total_student_id,total.total_term,student.student_name,staff.staff_name from total INNER JOIN student ON total.total_student_id=student.student_id INNER JOIN staff  ON total.total_staff_id=staff.staff_id";
		String sql = "SELECT * FROM total "
				+ "INNER JOIN student ON total.total_student_id = student.student_id "
				+ "LEFT JOIN `group` b ON total.total_student_id = b.group_student_id "
				+ "LEFT JOIN clazz c ON b.group_clazz_id = c.clazz_id "
				+ "WHERE total.total_no_money = 0";
		List<Object> params = new ArrayList<>();
		sql = buildAllSqlAndParams(params, sql, studentName);
		sql += " order by total.total_id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		return queryUserList(sql, params.toArray());
	}
	private String buildAllSqlAndParams(List<Object> params, String sql,String studentName) {
		//params.add(status);
		if (!StringUtils.isNullOrEmpty(studentName)) {
			sql += " and student.student_name like ? ";
			params.add("%" + studentName + "%");
		}

		return sql;
	}
	
	private String buildSqlAndParams(List<Object> params, String sql,String studentName) {
		//params.add(status);
		if (!StringUtils.isNullOrEmpty(studentName)) {
			sql += " and student.student_name like ? ";
			params.add("%" + studentName + "%");
		}

		return sql;
	}
	
	private List<TotalService> queryUserList(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> listData = Jdbc.getInstace().executeQueryForList(sql, params);
		List<TotalService> TotalServiceList = null;
		if (listData != null && listData.size() > 0) {
			TotalServiceList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				TotalServiceList.add(buildTotalService(data));
			}
		}
		return TotalServiceList;
	}
	
	private TotalService buildTotalService(Map<String, Object> data) {
		TotalService TotalServiceservice = new TotalService();
		TotalServiceservice.setTotalStudentName((String)data.get("student_name"));
		TotalServiceservice.setTotalStaffName((String)data.get("staff_name"));
		TotalServiceservice.setTotalClassName((String)data.get("clazz_name"));
		if (data.get("total_id") != null) {
			TotalServiceservice.setTotalId(Integer.parseInt(data.get("total_id").toString()));
			TotalServiceservice.setTotalIfLoan(Integer.parseInt(data.get("total_if_loan").toString()));
			TotalServiceservice.setTotalMoney(Double.parseDouble(data.get("total_money").toString()));
			TotalServiceservice.setTotalNoMoney(Double.parseDouble(data.get("total_no_money").toString()));
			TotalServiceservice.setTotalStaffId(Integer.parseInt(data.get("total_staff_id").toString()));
			TotalServiceservice.setTotalStudentId(Integer.parseInt(data.get("total_student_id").toString()));
			TotalServiceservice.setTotalTerm(Integer.parseInt(data.get("total_term").toString()));
		}
		return TotalServiceservice;
	}
	/**
	 * 转化Staff
	 */
	private List<Staff> changeStaff(List<Map<String,Object>>list) {
		List<Staff>listStaff=null;
		if (list!=null&&list.size()>0) {
			listStaff=new ArrayList<>();
			for (Map<String,Object> map : list) {
				listStaff.add(new Staff(
							(String)map.get("staff_name"),
							Integer.parseInt(map.get("staff_id").toString())
						));
			}
		}
		return listStaff;
	}
	/**
	 * 转化Receipt
	 */
	public List<ReceiptService> changeReceipt(List<Map<String,Object>>list){
		List<ReceiptService>receiptList=null;
		if (list!=null&&list.size()>0) {
			receiptList=new ArrayList<>();
			for (Map<String,Object> map : list) {
				String d=map.get("receipt_date").toString().split(" ")[0];
				receiptList.add(new ReceiptService(
						Integer.parseInt(map.get("receipt_id").toString()),
						Double.parseDouble(map.get("receipt_money").toString()),
						Integer.parseInt(map.get("receipt_staff_id").toString()),
						Integer.parseInt(map.get("receipt_total_id").toString()),
						java.sql.Date.valueOf(d),
						(String)map.get("student_name"),
						(String)map.get("staff_name")
						));
			}
		}
		return receiptList;
		
	}
}
